

/****** Object:  StoredProcedure [dbo].[SP_recMenu_GridPage_Hierarchy]    Script Date: 05/10/2014 08:21:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		Beni Khaeroni (jhung.ben@gmail.com)
-- Create date: 09 Mei 2014 02:23 PM
-- Description:	
-- =============================================
   CREATE PROCEDURE [dbo].[SP_recMenu_GridPage_Hierarchy]
   @param [varchar] (max)
   AS
   BEGIN
   DECLARE @SQL nvarchar(max);
   SET @SQL = '
       WITH n(MenuID, name,  Url, OrderSeq, Level, parent, Paths) AS
                        (
                         SELECT MenuID , MenuName as name, Url, OrderSeq, 0 Level,
                                   ParentMenuID as parent,
                                   '+''''+'('+''''+'+'+'CONVERT(VARCHAR (MAX), MenuID)'++'+'+''''+' - '+''''+'+'+'CONVERT(VARCHAR (MAX), OrderSeq)'++'+'+''''+')'+'''' +'Paths
                                    FROM master.recMenu
                                    WHERE ParentMenuID is null
                                    UNION ALL
                                    SELECT m.MenuID, m.MenuName, m.Url, m.OrderSeq, Level + 1, m.ParentMenuID,
                                    n.Paths'+'+'+''''+' * ('+''''+'+'++'CONVERT (VARCHAR (MAX), case when ISNULL(m.ParentMenuID, 0) = 0 then 0 else m.MenuID END)'+'+'+''''+' - '++''''+'+'+'CONVERT(VARCHAR 
                                    (MAX), m.OrderSeq)'+'+'++''''+')'+''''+' as Paths
                                    FROM master.recMenu as m, n
                                    WHERE n.MenuID = m.ParentMenuID
                                    )
                                    SELECT  n.MenuID,
                                    REPLICATE ('+''''+'     '+''''+' , n.Level) + n.Name AS MenuName,
                                     n.Url,
									n.OrderSeq,
									n.[Level],
									n.Parent as ParentMenuID,
									n.Name as ParentName,
									Paths FROM n   '
    
    
    
      if (@param  <> '')
      BEGIN
		SET @SQL = @SQL + @param
      END
      SET @SQL = @SQL + ' ORDER BY paths asc'
    exec sp_executesql @SQL
    print @SQL
 END
 
                       
GO


